package bar.dark.lee.insert;

import java.math.BigDecimal;
import java.text.Bidi;
import java.util.ArrayList;
import java.util.HashMap;

import bar.dark.lee.BDConstents;
import bar.dark.lee.common.BDUtil;
import bar.dark.lee.common.DBSQLite;
import bar.dark.lee.common.DBSQLite.ParseSelectData;
import bar.dark.lee.select.SelectData;
import android.content.Context;
import android.database.Cursor;

public class BalanceQuery
{
	public static final String INFOR_NO = "정보없음";
	
	private Context context;
	private int linkId;
	private DBSQLite sql;
	private String subMoney = "";
	private String realMoney = "";
	private int checkId;
	private String columnName = "";
	private String balanceMoney = "";
	private String balanceRealMoney = "";
	private int inoutType = -1;
	
	private ArrayList<BalanceData> balenceList = null;
	private int subId = -1;
	
	private SelectData selectData = null;
	
	public BalanceQuery(Context context)
	{
		this.context = context;
		this.sql = DBSQLite.getInstance(context);  
	}
	
	/**
	 * 잔액을 반환한다.
	 * @param id
	 * @param flag 원을 붙일지 안붙일지를 정한다. 0 = 붙임, 1 = 안붙임
	 * @return
	 */
	public String getBalanceMoney(String id)
	{
		balanceMoney = "";
		
		sql.selectMapTile(context, "SELECT submoney FROM " +
				"(SELECT s.submoney submoney FROM in_out o, in_out_sub s WHERE " +
				"o.id = s.linkid and s.linkid = " + id + ")", new ParseSelectData()
		{
			@Override
			public void resultData(Cursor c)
			{
				int subMoneyIdx = c.getColumnIndex("submoney");
				
				c.moveToFirst();
				
				for (int i = 0; i < c.getCount(); i++)
				{
					balanceMoney = c.getString(subMoneyIdx);
					c.moveToNext();
				}
			}
		});
		
		if (balanceMoney.equals(""))
		{
			return INFOR_NO;
		}
		
		String rStr = BDUtil.getWon(balanceMoney) + " 원";
		
		return rStr;
	}
	
	/**
	 * 자산이동의 현금을 반환한다.
	 * @param id
	 * * @param flag 원을 붙일지 안붙일지를 정한다. 0 = 붙임, 1 = 안붙임
	 * @return
	 */
	public String getRealMoney(String id)
	{
		balanceRealMoney = "";
		
		sql.selectMapTile(context, "SELECT realmoney FROM " +
				"(SELECT s.realmoney realmoney FROM in_out o, in_out_sub s WHERE " +
				"o.id = s.linkid and s.linkid = " + id + ")", new ParseSelectData()
		{
			@Override
			public void resultData(Cursor c)
			{
				int realMoneyIdx = c.getColumnIndex("realmoney");
				
				c.moveToFirst();
				
				for (int i = 0; i < c.getCount(); i++)
				{
					balanceRealMoney = c.getString(realMoneyIdx);
					c.moveToNext();
				}
			}
		});
		
		if (balanceRealMoney.equals(""))
		{
			return INFOR_NO;
		}
		
		String rStr = BDUtil.getWon(balanceRealMoney) + " 원";
		
		return rStr;
	}
	
	/**
	 * 현재 잔액을 입력한다.
	 * @param type
	 * @param moneykind
	 * @param date
	 * @param money
	 * @param flag 0 = 지출,  1 = 수입.
	 * @return
	 */
	public boolean setBalance(String type, String moneykind)
	{
		linkId = -1;
		checkId = -1;
		columnName = "money";
		
		sql.selectMapTile(context, "SELECT MAX(id) maxid FROM in_out", new ParseSelectData()
		{
			@Override
			public void resultData(Cursor c)
			{
				int maxIdIdx = c.getColumnIndex("maxid");
				
				c.moveToFirst();
				
				for (int i = 0; i < c.getCount(); i++)
				{
					linkId = c.getInt(maxIdIdx);
					c.moveToNext();
				}
			}
		});
		
		String query = "";
		
		if (type.equals(BDConstents.REAL_TYPE))
		{
			query = "SELECT money FROM real_money";
			moneykind = BDConstents.REAL_MONEY;
		}
		else if (type.equals(BDConstents.BANK_TYPE))
		{
			query = "SELECT money FROM bankbook WHERE name = '" + BDUtil.getApostrophe(moneykind) + "';";
		}
		else if (type.equals(BDConstents.CREDIT_TYPE))
		{
			query = "SELECT outmoney FROM credit_card WHERE name = '" + BDUtil.getApostrophe(moneykind) + "';";
			columnName = "outmoney";
		}
		else if (type.equals(BDConstents.CHECK_TYPE))
		{
			sql.selectMapTile(context, "SELECT banklink FROM check_card WHERE name = '" 
					+ BDUtil.getApostrophe(moneykind) + "';", new ParseSelectData()
			{
				@Override
				public void resultData(Cursor c)
				{
					int banklinkIndex = c.getColumnIndex("banklink");
					
					c.moveToFirst();
					
					for (int i = 0; i < c.getCount(); i++)
					{
						checkId = c.getInt(banklinkIndex);
						c.moveToNext();
					}
				}
			});
			
			if (checkId == -1)
			{
				return false;
			}
			
			query = "SELECT money FROM bankbook WHERE id = " + checkId + ";";
		}
		
		sql.selectMapTile(context, query, new ParseSelectData()
		{
			@Override
			public void resultData(Cursor c)
			{
				int moneyIdIdx = c.getColumnIndex(columnName);
				
				c.moveToFirst();
				
				for (int i = 0; i < c.getCount(); i++)
				{
					subMoney = c.getString(moneyIdIdx);
					c.moveToNext();
				}
			}
		});
		
		sql.selectMapTile(context, "SELECT money FROM real_money", new ParseSelectData()
		{
			@Override
			public void resultData(Cursor c)
			{
				int moneyIdIdx = c.getColumnIndex("money");
				
				c.moveToFirst();
				
				for (int i = 0; i < c.getCount(); i++)
				{
					realMoney = c.getString(moneyIdIdx);
					c.moveToNext();
				}
			}
		});
		
		HashMap<String, String> map = new HashMap<String, String>();
		map.put("linkid", linkId + "");
		map.put("type", type);
		map.put("submoney", subMoney);
		map.put("name", moneykind);
		map.put("realmoney", realMoney);
		
		sql.insert(context, map, "in_out_sub");
		
		return true;
	}
	
	/**
	 * 현재 잔액으로 금액을 업데이트 한다.
	 * @param id
	 * @param type
	 * @param moneykind
	 * @return
	 */
	public boolean setClearBalance(String id, String type, String moneykind)
	{
		linkId = -1;
		checkId = -1;
		columnName = "money";
		
		sql.selectMapTile(context, "SELECT MAX(id) maxid FROM in_out", new ParseSelectData()
		{
			@Override
			public void resultData(Cursor c)
			{
				int maxIdIdx = c.getColumnIndex("maxid");
				
				c.moveToFirst();
				
				for (int i = 0; i < c.getCount(); i++)
				{
					linkId = c.getInt(maxIdIdx);
					c.moveToNext();
				}
			}
		});
		
		String query = "";
		
		if (type.equals(BDConstents.REAL_TYPE))
		{
			query = "SELECT money FROM real_money";
			moneykind = BDConstents.REAL_MONEY;
		}
		else if (type.equals(BDConstents.BANK_TYPE))
		{
			query = "SELECT money FROM bankbook WHERE name = '" + BDUtil.getApostrophe(moneykind) + "';";
		}
		else if (type.equals(BDConstents.CREDIT_TYPE))
		{
			query = "SELECT outmoney FROM credit_card WHERE name = '" + BDUtil.getApostrophe(moneykind) + "';";
			columnName = "outmoney";
		}
		else if (type.equals(BDConstents.CHECK_TYPE))
		{
			sql.selectMapTile(context, "SELECT banklink FROM check_card WHERE name = '" 
					+ BDUtil.getApostrophe(moneykind) + "';", new ParseSelectData()
			{
				@Override
				public void resultData(Cursor c)
				{
					int banklinkIndex = c.getColumnIndex("banklink");
					
					c.moveToFirst();
					
					for (int i = 0; i < c.getCount(); i++)
					{
						checkId = c.getInt(banklinkIndex);
						c.moveToNext();
					}
				}
			});
			
			if (checkId == -1)
			{
				return false;
			}
			
			query = "SELECT money FROM bankbook WHERE id = " + checkId + ";";
		}
		
		sql.selectMapTile(context, query, new ParseSelectData()
		{
			@Override
			public void resultData(Cursor c)
			{
				int moneyIdIdx = c.getColumnIndex(columnName);
				
				c.moveToFirst();
				
				for (int i = 0; i < c.getCount(); i++)
				{
					subMoney = c.getString(moneyIdIdx);
					c.moveToNext();
				}
			}
		});
		
		sql.selectMapTile(context, "SELECT money FROM real_money", new ParseSelectData()
		{
			@Override
			public void resultData(Cursor c)
			{
				int moneyIdIdx = c.getColumnIndex("money");
				
				c.moveToFirst();
				
				for (int i = 0; i < c.getCount(); i++)
				{
					realMoney = c.getString(moneyIdIdx);
					c.moveToNext();
				}
			}
		});
		
		HashMap<String, String> map = new HashMap<String, String>();
		map.put("type", type);
		map.put("submoney", subMoney);
		map.put("name", moneykind);
		map.put("realmoney", realMoney);
		
		String [] whereArgs = new String[]{id};
		
		sql.update(context, "in_out_sub", map, "id = ?", whereArgs);
		
		return true;
	}
	
	/**
	 * 잔액 테이블의 데이터를 롤백한다.
	 * @param id
	 * @param type
	 * @param moneykind
	 * @param money
	 * @param flag 0 = 금액을 뺀다. 1 = 금액을 더한다. 
	 * @return
	 */
	public boolean setBalanceUpdateRollBank
	(
			String id, String type, String moneykind, String money, int flag)
	{
		balenceList = null;
		subId = -1;
//		inoutType = -1;
//		
//		sql.selectMapTile(context, "SELECT type FROM in_out WHERE id = " + id + ";", new ParseSelectData()
//		{
//			@Override
//			public void resultData(Cursor c)
//			{
//				int typeIdx = c.getColumnIndex("type");
//				
//				c.moveToFirst();
//				
//				for (int i = 0; i < c.getCount(); i++)
//				{
//					inoutType = c.getInt(typeIdx);
//					c.moveToNext();
//				}
//			}
//		});
		
		sql.selectMapTile(context, "SELECT id FROM in_out_sub WHERE linkid = " + id + ";", new ParseSelectData()
		{
			@Override
			public void resultData(Cursor c)
			{
				int idIdx = c.getColumnIndex("id");
				
				c.moveToFirst();
				
				for (int i = 0; i < c.getCount(); i++)
				{
					subId = c.getInt(idIdx);
					c.moveToNext();
				}
			}
		});
		
		if (subId == -1)
		{
			return false;
		}
		
		String query = "";
		
		if (type.equals(BDConstents.REAL_TYPE))
		{
			moneykind = BDConstents.REAL_MONEY;
		}
		
		query = "SELECT MAX(id) id, submoney, realmoney FROM in_out_sub WHERE id < " + subId + 
				" and id > 0 and type = " + type +	" and name = '" + BDUtil.getApostrophe(moneykind) + "';";
		
		sql.selectMapTile(context, query, new ParseSelectData()
		{
			@Override
			public void resultData(Cursor c)
			{
				balenceList = new ArrayList<BalanceData>();
				
				int idIdx = c.getColumnIndex("id");
				int submoneyIdx = c.getColumnIndex("submoney");
				int realmoneyIdx = c.getColumnIndex("realmoney");
				
				c.moveToFirst();
				
				for (int i = 0; i < c.getCount(); i++)
				{
					BalanceData data = new BalanceData();
					data.setId(c.getInt(idIdx) + "");
					data.setSubmoney(c.getString(submoneyIdx));
					data.setRealmoney(c.getString(realmoneyIdx));
					balenceList.add(data);
					c.moveToNext();
				}
			}
		});
		
		if (balenceList.size() >= 1 && balenceList.get(0).getSubmoney() != null)
		{
			BalanceData data = balenceList.get(0);
			
			BigDecimal submoney = new BigDecimal(data.getSubmoney());
			BigDecimal realmoney = new BigDecimal(data.getRealmoney());
			
			if (flag == 0)
			{
				submoney = submoney.subtract(new BigDecimal(money));
				realmoney = realmoney.add(new BigDecimal(money));
			}
			else
			{
				submoney = submoney.add(new BigDecimal(money));
				realmoney = realmoney.subtract(new BigDecimal(money));
			}
			
			HashMap<String, String> map = new HashMap<String, String>();
			map.put("type", type);
			map.put("submoney", submoney.toString());
			map.put("name", moneykind);
			map.put("realmoney", realmoney.toString());
			
			String [] whereArgs = new String[]{subId + ""};
			
			sql.update(context, "in_out_sub", map, "id = ?", whereArgs);
		}
		else
		{
			query = "SELECT MIN(id) id, submoney, realmoney FROM in_out_sub WHERE id > " + subId + 
					" and type = " + type +	" and name = '" + BDUtil.getApostrophe(moneykind) + "';";
			
			sql.selectMapTile(context, query, new ParseSelectData()
			{
				@Override
				public void resultData(Cursor c)
				{
					balenceList = new ArrayList<BalanceData>();
					
					int idIdx = c.getColumnIndex("id");
					int submoneyIdx = c.getColumnIndex("submoney");
					int realmoneyIdx = c.getColumnIndex("realmoney");
					
					c.moveToFirst();
					
					for (int i = 0; i < c.getCount(); i++)
					{
						BalanceData data = new BalanceData();
						data.setId(c.getInt(idIdx) + "");
						data.setSubmoney(c.getString(submoneyIdx));
						data.setRealmoney(c.getString(realmoneyIdx));
						balenceList.add(data);
						c.moveToNext();
					}
				}
			});
			
			if (balenceList.size() >= 1 && balenceList.get(0).getSubmoney() != null)
			{
				BalanceData data = balenceList.get(0);
				
				BigDecimal submoney = new BigDecimal(data.getSubmoney());
				BigDecimal realmoney = new BigDecimal(data.getRealmoney());
				
				if (flag == 0)
				{
					submoney = submoney.add(new BigDecimal(money));
					realmoney = realmoney.subtract(new BigDecimal(money));
				}
				else
				{
					submoney = submoney.subtract(new BigDecimal(money));
					realmoney = realmoney.add(new BigDecimal(money));
				}
				
				HashMap<String, String> map = new HashMap<String, String>();
				map.put("type", type);
				map.put("submoney", submoney.toString());
				map.put("name", moneykind);
				map.put("realmoney", realmoney.toString());
				
				String [] whereArgs = new String[]{subId + ""};
				
				sql.update(context, "in_out_sub", map, "id = ?", whereArgs);
			}
			else
			{
				// 앞에도 없고 뒤에도 없으면 현재 금액을 넣어준다.
				setClearBalance(subId + "", type, moneykind);
			}
		}
		
		balenceList.clear();
		
		query = "SELECT id, submoney, realmoney FROM in_out_sub WHERE " +
				"id > " + subId + " and type = " + type +
				" and name = '" + BDUtil.getApostrophe(moneykind) + "';";
		
		sql.selectMapTile(context, query, new ParseSelectData()
		{
			@Override
			public void resultData(Cursor c)
			{
				balenceList = new ArrayList<BalanceData>();
				
				int idIdx = c.getColumnIndex("id");
				int submoneyIdx = c.getColumnIndex("submoney");
				int realmoneyIdx = c.getColumnIndex("realmoney");
				
				c.moveToFirst();
				
				for (int i = 0; i < c.getCount(); i++)
				{
					BalanceData data = new BalanceData();
					data.setId(c.getInt(idIdx) + "");
					data.setSubmoney(c.getString(submoneyIdx));
					data.setRealmoney(c.getString(realmoneyIdx));
					balenceList.add(data);
					c.moveToNext();
				}
			}
		});
		
		if (balenceList.size() < 1)
		{
			return false;
		}
		
		for (int i = 0; i < balenceList.size(); i++)
		{
			BalanceData data = balenceList.get(i);
			
			BigDecimal submoney = new BigDecimal(data.getSubmoney());
			BigDecimal realmoney = new BigDecimal(data.getRealmoney());
			
			if (flag == 0)
			{
				submoney = submoney.subtract(new BigDecimal(money));
				realmoney = realmoney.add(new BigDecimal(money));
			}
			else
			{
				submoney = submoney.add(new BigDecimal(money));
				realmoney = realmoney.subtract(new BigDecimal(money));
			}
			
			HashMap<String, String> map = new HashMap<String, String>();
			map.put("type", type);
			map.put("submoney", submoney.toString());
			map.put("name", moneykind);
			map.put("realmoney", realmoney.toString());
			
			String [] whereArgs = new String[]{data.getId() + ""};
			
			sql.update(context, "in_out_sub", map, "id = ?", whereArgs);
		}
		
		return true;
	}
	
	/**
	 * 잔액 테이블에 있는 데이터를 지운다.
	 * @param id
	 * @param type
	 * @param moneykind
	 * @param money
	 * @param flag 0 = 금액을 뺀다. 1 = 금액을 더한다. 
	 * @return
	 */
	public boolean setbalanceDelete(String id, String type, String moneykind, String money, int flag)
	{
		setBalanceUpdateRollBank(id, type, moneykind, money, flag);
		
		String [] whereArgs = new String[]{id}; 
		
		sql.delete(context, "in_out_sub", "linkid = ?", whereArgs);
		
		return true;
	}
}
